﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data.OleDb;
using DTO;

namespace DAO
{
    public class HD_ChiTietDAO : ConnectionDAO
    {
        public static bool LapCTHD(long mHD, long mPT, long sN)
        {
            OleDbConnection cN = null;
            bool temp = true;

            try
            {
                cN = Cnect();

                string cmString = @"INSERT INTO CHITIET_HD(MaHoaDon,MaPhieuThue,SoNgayThue) VALUES (@mHD,@mPT,@sN)";

                OleDbCommand cM = new OleDbCommand(cmString, cN);

                OleDbParameter thamSo;

                thamSo = new OleDbParameter("@mHD", OleDbType.Integer);
                thamSo.Value = mHD;
                cM.Parameters.Add(thamSo);

                thamSo = new OleDbParameter("@mPT", OleDbType.Integer);
                thamSo.Value = mPT;
                cM.Parameters.Add(thamSo);

                thamSo = new OleDbParameter("@sN", OleDbType.Integer);
                thamSo.Value = sN;
                cM.Parameters.Add(thamSo);

                cM.ExecuteNonQuery();

            }
            catch (Exception ex)
            {
                temp = false;
            }
            finally
            {
                if (cN != null && cN.State == System.Data.ConnectionState.Open)
                    cN.Close();
            }

            return temp;
        }

        public static long UpdateTien(long mPT, double tT)
        {
            long temp = 1;
            OleDbConnection cN = null;

            try
            {
                cN = Cnect();

                string cmString = @"UPDATE CHITIET_HD
                                    SET ThanhTien=@tT
                                    WHERE MaPhieuThue=@mPT";

                OleDbCommand cM = new OleDbCommand(cmString, cN);

                OleDbParameter thamSo;






                thamSo = new OleDbParameter("@tT", OleDbType.Double);
                thamSo.Value = tT;
                cM.Parameters.Add(thamSo);


                thamSo = new OleDbParameter("@mPT", OleDbType.Integer);
                thamSo.Value = mPT;
                cM.Parameters.Add(thamSo);

                cM.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                temp = 0;
            }
            finally
            {
                if (cN != null && cN.State == System.Data.ConnectionState.Open)
                    cN.Close();
            }

            return temp;
        }

        public static HD_ChiTietDTO LayTTTheoMPT(long mPT)
        {
            OleDbConnection cN = null;
            HD_ChiTietDTO CTHD = new HD_ChiTietDTO();

            try
            {
                cN = Cnect();

                string cmString = @"SELECT CTHD.MaCT_HD,CTHD.MaHoaDon,CTHD.MaPhieuThue,CTHD.SoNgayThue,CTHD.ThanhTien
                                    FROM CHITIET_HD CTHD
                                    WHERE CTHD.MaPhieuThue=@mPT";


                OleDbCommand cM = new OleDbCommand(cmString, cN);

                OleDbParameter thamSo = new OleDbParameter("@mPT", OleDbType.Integer);
                thamSo.Value = mPT;

                cM.Parameters.Add(thamSo);

                OleDbDataReader rDer = cM.ExecuteReader();

                while (rDer.Read())
                {
                    if (!rDer.IsDBNull(0))
                        CTHD.MaCT_HD = rDer.GetInt32(0);
                    if (!rDer.IsDBNull(1))
                        CTHD.MaHoaDon.MaHoaDon = rDer.GetInt32(1);
                    if (!rDer.IsDBNull(2))
                        CTHD.MaPhieuThue.MaPhieuThue = rDer.GetInt32(2);
                    if (!rDer.IsDBNull(3))
                        CTHD.SoNgayThue = rDer.GetInt32(3);
                    if (!rDer.IsDBNull(4))
                        CTHD.ThanhTien = rDer.GetDouble(4);

                }
            }
            catch (Exception ex)
            {
                CTHD = new HD_ChiTietDTO();
            }
            finally
            {
                if (cN != null && cN.State == System.Data.ConnectionState.Open)
                    cN.Close();
            }

            return CTHD;

        }


        public static List<HD_ChiTietDTO> LayDSHDCTTheoMHD(long mHD)
        {
            OleDbConnection cN = null;
            List<HD_ChiTietDTO> LHD = new List<HD_ChiTietDTO>();

            try
            {
                cN = Cnect();

                string cmString = @"SELECT DISTINCT CT.MaCT_HD,CT.MaHoaDon,CT.MaPhieuThue,CT.SoNgayThue,CT.ThanhTien
                                    FROM CHITIET_HD CT
                                    WHERE CT.MaHoaDon=@mHD
                                    ORDER BY CT.MaCT_HD asc";


                OleDbCommand cM = new OleDbCommand(cmString, cN);

                OleDbParameter thamSo = new OleDbParameter("@mHD", OleDbType.Integer);
                thamSo.Value = mHD;

                cM.Parameters.Add(thamSo);

                OleDbDataReader rDer = cM.ExecuteReader();

                while (rDer.Read())
                {
                    HD_ChiTietDTO CTHD = new HD_ChiTietDTO();

                    if (!rDer.IsDBNull(0))
                        CTHD.MaCT_HD = rDer.GetInt32(0);
                    if (!rDer.IsDBNull(1))
                        CTHD.MaHoaDon.MaHoaDon = rDer.GetInt32(1);
                    if (!rDer.IsDBNull(2))
                        CTHD.MaPhieuThue.MaPhieuThue = rDer.GetInt32(2);
                    if (!rDer.IsDBNull(3))
                        CTHD.SoNgayThue = rDer.GetInt32(3);
                    if (!rDer.IsDBNull(4))
                        CTHD.ThanhTien = rDer.GetDouble(4);

                    LHD.Add(CTHD);
                }
            }
            catch (Exception ex)
            {
                LHD = new List<HD_ChiTietDTO>();
            }
            finally
            {
                if (cN != null && cN.State == System.Data.ConnectionState.Open)
                    cN.Close();
            }

            return LHD;
        }

        public static List<HD_ChiTietDTO> LayDSTheoMLP(long mLP)
        {
            OleDbConnection cN = null;
            List<HD_ChiTietDTO> LHD = new List<HD_ChiTietDTO>();

            try
            {
                cN = Cnect();

                string cmString = @"SELECT DISTINCT CT.MaCT_HD,CT.MaHoaDon,CT.MaPhieuThue,CT.SoNgayThue,CT.ThanhTien
                                    FROM CHITIET_HD CT,PHIEUTHUE PT,PHONG P
                                    WHERE CT.MaPhieuThue=PT.MaPhieuThue AND PT.MaPhong=P.MaPhong AND P.MaLoaiPhong=@mLP 
                                    ORDER BY CT.MaCT_HD asc";


                OleDbCommand cM = new OleDbCommand(cmString, cN);

                OleDbParameter thamSo = new OleDbParameter("@mLP", OleDbType.Integer);
                thamSo.Value = mLP;

                cM.Parameters.Add(thamSo);

                OleDbDataReader rDer = cM.ExecuteReader();

                while (rDer.Read())
                {
                    HD_ChiTietDTO CTHD = new HD_ChiTietDTO();

                    if (!rDer.IsDBNull(0))
                        CTHD.MaCT_HD = rDer.GetInt32(0);
                    if (!rDer.IsDBNull(1))
                        CTHD.MaHoaDon.MaHoaDon = rDer.GetInt32(1);
                    if (!rDer.IsDBNull(2))
                        CTHD.MaPhieuThue.MaPhieuThue = rDer.GetInt32(2);
                    if (!rDer.IsDBNull(3))
                        CTHD.SoNgayThue = rDer.GetInt32(3);
                    if (!rDer.IsDBNull(4))
                        CTHD.ThanhTien = rDer.GetDouble(4);

                    LHD.Add(CTHD);
                }
            }
            catch (Exception ex)
            {
                LHD = new List<HD_ChiTietDTO>();
            }
            finally
            {
                if (cN != null && cN.State == System.Data.ConnectionState.Open)
                    cN.Close();
            }

            return LHD;
        }

        public static List<HD_ChiTietDTO> LayDSTheoMP(long mP)
        {
            OleDbConnection cN = null;
            List<HD_ChiTietDTO> LHD = new List<HD_ChiTietDTO>();

            try
            {
                cN = Cnect();

                string cmString = @"SELECT DISTINCT CT.MaCT_HD,CT.MaHoaDon,CT.MaPhieuThue,CT.SoNgayThue,CT.ThanhTien
                                    FROM CHITIET_HD CT,PHIEUTHUE PT
                                    WHERE CT.MaPhieuThue=PT.MaPhieuThue AND PT.MaPhong=@mP
                                    ORDER BY CT.MaCT_HD asc";


                OleDbCommand cM = new OleDbCommand(cmString, cN);

                OleDbParameter thamSo = new OleDbParameter("@mP", OleDbType.Integer);
                thamSo.Value = mP;

                cM.Parameters.Add(thamSo);

                OleDbDataReader rDer = cM.ExecuteReader();

                while (rDer.Read())
                {
                    HD_ChiTietDTO CTHD = new HD_ChiTietDTO();

                    if (!rDer.IsDBNull(0))
                        CTHD.MaCT_HD = rDer.GetInt32(0);
                    if (!rDer.IsDBNull(1))
                        CTHD.MaHoaDon.MaHoaDon = rDer.GetInt32(1);
                    if (!rDer.IsDBNull(2))
                        CTHD.MaPhieuThue.MaPhieuThue = rDer.GetInt32(2);
                    if (!rDer.IsDBNull(3))
                        CTHD.SoNgayThue = rDer.GetInt32(3);
                    if (!rDer.IsDBNull(4))
                        CTHD.ThanhTien = rDer.GetDouble(4);

                    LHD.Add(CTHD);
                }
            }
            catch (Exception ex)
            {
                LHD = new List<HD_ChiTietDTO>();
            }
            finally
            {
                if (cN != null && cN.State == System.Data.ConnectionState.Open)
                    cN.Close();
            }

            return LHD;
        }
    }
}
